package com.minisoft.utils.excel;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.GlobalDbConfig;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.minisoft.utils.GateWayDispatcher;
import org.junit.Test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author JueSu
 * @description: TODO
 * @date 2022/6/29 12:39
 */
public class ExcelTest {
//    public static String DB_CENTER = "center-pro";
    public static String DB_CENTER = "center-dev";

    @Test
    public void testBindTelList() throws SQLException {
        GateWayDispatcher gateWayDispatcher = new GateWayDispatcher();
        gateWayDispatcher.addMappingConfig("/**",
//                "https://clink-openapi.cnhuafas.com","082615fa8922b3836d36656f69d75a6e","6XKwr54j5F43mW1CN1V6");
                "https://clink-openapi-test.cnhuafas.com", "1438ff588427d93d10a092b896a34ea4", "4w8Xo4AF25f669YB95l5");
        JSONObject p = new JSONObject();
        p.set("cno", "1317").set("telType",2);
        String s1 = gateWayDispatcher.doSignGet("/list_client_tels", p);
        Console.log(s1);
    }
    @Test
    public void testBindTel() throws SQLException {
        GateWayDispatcher gateWayDispatcher = new GateWayDispatcher();
        gateWayDispatcher.addMappingConfig("/**",
//                "https://clink-openapi.cnhuafas.com","082615fa8922b3836d36656f69d75a6e","6XKwr54j5F43mW1CN1V6");
                "https://clink-openapi-test.cnhuafas.com", "1438ff588427d93d10a092b896a34ea4", "4w8Xo4AF25f669YB95l5");
        JSONObject p = new JSONObject();
        p.set("username", "liangmengting");
        String s1 = gateWayDispatcher.doSignGet("/describe_client", p);
        Console.log(s1);
        String bindTel = "13580450193";
        p.set("bindType",1)
                .set("bindTel",bindTel).set("businessLine","YXX")
                .set("platform","HZG");
        s1 = gateWayDispatcher.doSignPost("/online", p.toString());
        Console.log(s1);
        s1 = gateWayDispatcher.doSignGet("/describe_client", p);
        Console.log(s1);

    }
    @Test
    public void testTelBelong() throws SQLException {
        GateWayDispatcher gateWayDispatcher = new GateWayDispatcher();
        gateWayDispatcher.addMappingConfig("/**",
//                "https://clink-openapi.cnhuafas.com","082615fa8922b3836d36656f69d75a6e","6XKwr54j5F43mW1CN1V6");
                "https://clink-openapi-test.cnhuafas.com","1438ff588427d93d10a092b896a34ea4","4w8Xo4AF25f669YB95l5");
        JSONObject p = new JSONObject();
        p.set("username","liangmengting");
        String s1 = gateWayDispatcher.doSignGet("/describe_client", p);
        for (int i = 10;i < 13;i++) {
            int limit = 100,offset = 0;
            DateTime dateTime = DateUtil.parseDate(StrUtil.format("2022-{}-01",i));
            long begin = DateUtil.beginOfMonth(dateTime).getTime() / 1000;
            long end = DateUtil.endOfMonth(dateTime).getTime() / 1000;
            p.set("startTime",begin).set("endTime",end).set("limit",limit).set("businessLineAndSystem","YXX_HZG");
            p.set("customerNumber","18520497575").set("type",2);
            JSONArray cdrs = null;
            Map<String,Boolean> doneMobile = new HashMap<>();
            do {
                p.set("offset",offset);
                String s = gateWayDispatcher.doSignGet("/list_cdr_all", p);
                cdrs = JSONUtil.parseObj(s).getJSONArray("cdrs");
                System.out.println(cdrs.size());
                List<Object[]> args = new ArrayList<>();
                for (int i1 = 0; i1 < cdrs.size(); i1++) {
                    JSONObject cdr = cdrs.getJSONObject(i1);
                    String customerCity = cdr.getStr("customerCity");
                    String customerNumber = cdr.getStr("customerNumber");
                    if (!doneMobile.containsKey(customerNumber)) {
                        args.add(new Object[]{customerCity,customerNumber});
                        doneMobile.put(customerNumber,true);
                    }
                }
//                int[] ints = Db.use(DB_CENTER).executeBatch("update customer_call c,customer_potential p set c.tel_belong = ? where c.potential_id = p.id and p.mobile = ?", args.toArray(new Object[][]{}));
//                Console.log("{} result {}",i,ints);
                offset += limit;
            } while (cdrs.size() == limit);
        }
    }

    @Test
    public void testProjectId() throws SQLException {
        List<Entity> entityList = Db.use(DB_CENTER).query("select distinct cr.clue_id ,r.project_id ,cr.customer_opportunity_id ,r.project_name ,cor.order_time \n" +
                "from cp_allocation_clue c \n" +
                "join customer_report cr on cr.clue_id = c.clue_id \n" +
                "join customer_order_report cor on cor.customer_opportunity_id  = cr.customer_opportunity_id  \n" +
                "join sale_team_sale_org_rel r on r.project_id = cor.project_id and r.deleted = 0\n" +
                "where c.deal = 1 order by cor.order_time");

        for (Entity entity : entityList) {
            Console.log("update cp_allocation_clue set deal_time = '{}' ,project_id = '{}' ,project_name = '{}' where clue_id = '{}';",entity.get("order_time"),entity.get("project_id")
                    ,entity.get("project_name"),entity.get("clue_id"));
        }

    }

    @Test
    public void testWrite() throws SQLException {

        ExcelWriter writer = ExcelUtil.getWriter("C:\\Users\\31594\\Downloads\\2022风控刷证客户（新科技）.xlsx");
        for (int i = 1; i < writer.getRowCount(); i++) {
            String pid = writer.getCell(0, i).getStringCellValue();
            String mbile = writer.getCell( 3,i).getStringCellValue();

            //7 8
            Entity entity = Db.use(DB_CENTER).queryOne("select co.visit_first_date ,co.create_time,co.visit_channel_name,cm.mobile from customer_opportunity co,customer_mobile cm ,sale_team_sale_org_rel r  where co.customer_opportunity_id = cm.customer_opportunity_id and co.sale_team_id = r.sale_team_id and r.project_id = ? and cm.mobile = ? and cm.deleted = 0 and cm.sort = 0",pid,mbile);
            Console.log("{} {}" , i,mbile);
            if (entity != null) {
                String visit_first_date = entity.getStr("visit_first_date");
                String visit_channel_name = entity.getStr("visit_channel_name");
                String create_time = entity.getStr("create_time");
                writer.writeCellValue(7,i,visit_first_date).writeCellValue(8,i,visit_channel_name)
                        .writeCellValue(9,i,create_time);
            }
        }
        writer.flush();
        writer.close();
    }

//    static String exhHost = "http://customer-exhibition-dev.packageasdev.huafagroup.com";
    static String exhHost = "https://exhibition.cnhuafas.com";

    /**
     * 初始展厅数据
     */
    @Test
    public void initExb() throws SQLException {

        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\31594\\Downloads\\展厅信息登记(1).xlsx");
        int i = 1;
        JSONArray bindList = new JSONArray();
        GlobalDbConfig.setCaseInsensitive(false);
        List<Entity> entityList = Db.use(DB_CENTER).query("select distinct r.area_id areaId,r.area_name areaName,r.city_id cityId,r.city_name cityName,r.sale_team_id saleTeamId,r.sale_team_name saleTeamName from sale_team_sale_org_rel r where r.real_city_name  = '珠海' and r.deleted = 0");
        for (Entity entity : entityList) {
            JSONObject obj = JSONUtil.parseObj(entity);
            bindList.add(obj);
        }

        for (Map<String, Object> row : reader.readAll()) {
            Object name = row.get("展厅名称");
            Object province = row.get("展厅所属省份（地理位置）");
            Object city = row.get("展厅所属城市（地理位置）");
            Object orgName = row.get("关联团队名称");
            JSONObject p = new JSONObject();
            Object orgId = row.get("orgId");
            p.set("city",city).set("name",name).set("operator","system").set("orgId",orgId).set("orgName",orgName)
                    .set("province",province).set("bindList",bindList);
            String result = HttpUtil.post(exhHost + "/exhInfo/saveOrUpdate", p.toString());
            Console.log("{}/{}" ,i++,result);
        }

    }
    @Test
    public void initXhs(){

        ExcelReader reader = ExcelUtil.getReader("D:\\temp\\副本小红书文案整理.xlsx");
        List<Map<String, Object>> maps = reader.readAll();
        for (Map<String, Object> map : maps) {
            String title = map.get("标题").toString();
            //把换行符去掉
            title = title.replaceAll("\\n","");
            System.out.println(title);
        }
    }
}
